/**
 * module:  配种记录
 * avator:   渣
 * table:  nc_descent,nc_livestock
 */
var express = require('express');
var router = express.Router();
var zSend = require('./controller');

const db = require('../../../db/db');
function queryAsync(sql, values) {
  return new Promise((resolve, reject) => {
    db.query(sql, values, (err, results) => {
      if (err) reject(err);
      else resolve(results);
    });
  });
}
// 配种记录列表
router.get('/list', async (req, res) => {
  try {
    let { currentPage = 1, pageSize = 5, bullInfo = '', cowInfo = '' } = req.query;
    currentPage = parseInt(currentPage, 10);
    pageSize = Math.min(parseInt(pageSize, 10), 100); // 限制最大页大小
    
    // 参数验证
    if (isNaN(currentPage) || isNaN(pageSize)) {
      return res.status(400).send(zSend('参数类型错误', 400));
    }
    
    let baseQuery = 'SELECT * FROM nc_descent WHERE 1=1';
    let countQuery = 'SELECT COUNT(*) as total FROM nc_descent WHERE 1=1';
    const values = [];
    const countValues = [];
    
    if (bullInfo && cowInfo) {
      const bullIds = await queryAsync('SELECT F_Id FROM nc_livestock WHERE F_Title LIKE ?', [`%${bullInfo}%`]);
      const cowIds = await queryAsync('SELECT F_Id FROM nc_livestock WHERE F_Title LIKE ?', [`%${cowInfo}%`]);
      
      if (bullIds.length === 0 || cowIds.length === 0) {
        return res.send(zSend({ list: [], total: 0, currentPage: 1, pageSize, totalPages: 0 }));
      }
      
      const bullPlaceholders = bullIds.map(() => '?').join(',');
      const cowPlaceholders = cowIds.map(() => '?').join(',');
      
      baseQuery += ` AND (F_PaternalLine IN (${bullPlaceholders}) OR F_Matriarchal IN (${cowPlaceholders}))`;
      countQuery += ` AND (F_PaternalLine IN (${bullPlaceholders}) OR F_Matriarchal IN (${cowPlaceholders}))`;
      
      const ids = [...bullIds.map(b => b.F_Id), ...cowIds.map(c => c.F_Id)];
      values.push(...ids);
      countValues.push(...ids);
    } else if (bullInfo) {
      const bullIds = await queryAsync('SELECT F_Id FROM nc_livestock WHERE F_Title LIKE ?', [`%${bullInfo}%`]);
      if (bullIds.length === 0) return res.send(zSend({ list: [], total: 0, currentPage: 1, pageSize, totalPages: 0 }));
      
      const bullPlaceholders = bullIds.map(() => '?').join(',');
      baseQuery += ` AND F_PaternalLine IN (${bullPlaceholders})`;
      countQuery += ` AND F_PaternalLine IN (${bullPlaceholders})`;
      
      values.push(...bullIds.map(b => b.F_Id));
      countValues.push(...bullIds.map(b => b.F_Id));
    } else if (cowInfo) {
      const cowIds = await queryAsync('SELECT F_Id FROM nc_livestock WHERE F_Title LIKE ?', [`%${cowInfo}%`]);
      if (cowIds.length === 0) return res.send(zSend({ list: [], total: 0, currentPage: 1, pageSize, totalPages: 0 }));
      
      const cowPlaceholders = cowIds.map(() => '?').join(',');
      baseQuery += ` AND F_Matriarchal IN (${cowPlaceholders})`;
      countQuery += ` AND F_Matriarchal IN (${cowPlaceholders})`;
      
      values.push(...cowIds.map(c => c.F_Id));
      countValues.push(...cowIds.map(c => c.F_Id));
    }
    
    // 执行总数统计查询
    const countResult = await queryAsync(countQuery, countValues);
    const total = countResult[0].total;
    const totalPages = Math.max(1, Math.ceil(total / pageSize)); // 确保至少有1页
    
    // 调整当前页为有效范围
    const effectivePage = Math.min(Math.max(1, currentPage), totalPages);
    const offset = (effectivePage - 1) * pageSize;
    
    // 执行分页查询
    baseQuery += ' LIMIT ? OFFSET ?';
    values.push(pageSize, offset);
    
    const list = await queryAsync(baseQuery, values);
    
    // 返回包含总数和列表的数据结构，同时提供分页信息
    res.send(zSend({
        total,
        list
    }));
  } catch (err) {
    console.error(err);
    res.status(500).send(zSend('服务器内部错误', 500));
  }
});

// 配种记录添加
router.post('/add', (req, res, next) => {
    const { F_PaternalLine, F_Matriarchal,F_ReproTime, F_Remark, F_UserName, F_Phone, F_Address } = req.body;
    if (!F_ReproTime) {
        res.send(zSend('参数错误', 400))
    }
    const F_Id = Date.now();
    const F_CreateTime = new Date().toLocaleString();
    const sql = `
        INSERT INTO nc_descent (
            F_Id,F_PaternalLine, F_Matriarchal,F_ReproTime, F_Remark, F_UserName, F_Phone, F_Address,F_CreateTime
        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
    `
    db.query(sql, [F_Id,F_PaternalLine, F_Matriarchal,F_ReproTime, F_Remark, F_UserName, F_Phone, F_Address,F_CreateTime], (err, data) => {
        if (!data) {
            res.send(zSend('服务器内部错误', 500))
        } else {
            res.send(zSend('添加成功'))
        }
    })
})
// 配种记录修改
router.post('/update', (req, res, next) => {
    const { F_Id, F_PaternalLine, F_Matriarchal,F_ReproTime, F_Remark, F_UserName, F_Phone, F_Address } = req.body;
      if (!F_ReproTime||!F_Id) {
        res.send(zSend('参数错误', 400))
    }
    const F_CreateTime = new Date().toLocaleString();
    const sql = `
        UPDATE nc_descent SET F_PaternalLine = ?, F_Matriarchal = ?, F_ReproTime = ?, F_Remark = ?, F_UserName = ?, F_Phone = ?, F_Address = ?, F_CreateTime = ? WHERE F_Id = ?
    `
    db.query(sql, [F_PaternalLine, F_Matriarchal,F_ReproTime, F_Remark, F_UserName, F_Phone, F_Address,F_CreateTime, F_Id], (err, data) => {
        if (!data) {
            res.send(zSend('服务器内部错误', 500))
        } else {
            res.send(zSend('修改成功'))
        }
    })
})

// 配种记录删除
router.post('/delete', (req, res, next) => {
    const { F_Id } = req.body;
    if (!F_Id) {
        res.send(zSend('参数错误', 400))
    }
    const sql = `
        delete from nc_descent where F_Id='${F_Id}'
    `
    db.query(sql, (err, data) => {
        if (!data) {
            res.send(zSend('服务器内部错误', 500))
        } else {
            res.send(zSend('删除成功'))
        }
    })
})

module.exports = router;